package org.jeecgframework.web.service.impl;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.DataUtils;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.LogUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.web.service.CbsBkCertificatesServiceI;
import org.jeecgframework.web.system.pojo.base.CbsBkCertificatesEntity;
import org.jeecgframework.web.system.pojo.base.ZyFileLogEntity;
import org.jeecgframework.web.util.ExcelUtil;
import org.jeecgframework.web.util.FileBean;
import org.jeecgframework.web.util.StaticValue;
import org.jeecgframework.web.util.UploadFileUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service("cbsBkCertificatesService")
@Transactional
public class CbsBkCertificatesServiceImpl extends CommonServiceImpl implements CbsBkCertificatesServiceI {
 	public <T> void delete(T entity) {
 		super.delete(entity);
 		//执行删除操作配置的sql增强
		this.doDelSql((CbsBkCertificatesEntity)entity);
 	}
 	
 	public <T> Serializable save(T entity) {
 		Serializable t = super.save(entity);
 		//执行新增操作配置的sql增强
 		this.doAddSql((CbsBkCertificatesEntity)entity);
 		return t;
 	}
 	
 	public <T> void saveOrUpdate(T entity) {
 		super.saveOrUpdate(entity);
 		//执行更新操作配置的sql增强
 		this.doUpdateSql((CbsBkCertificatesEntity)entity);
 	}
 	
 	/**
	 * 默认按钮-sql增强-新增操作
	 * @return
	 */
 	public boolean doAddSql(CbsBkCertificatesEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-更新操作
	 * @return
	 */
 	public boolean doUpdateSql(CbsBkCertificatesEntity t){
	 	return true;
 	}
 	/**
	 * 默认按钮-sql增强-删除操作
	 * @return
	 */
 	public boolean doDelSql(CbsBkCertificatesEntity t){
	 	return true;
 	}
 	
 	/**
	 * 替换sql中的变量
	 * @param sql
	 * @return
	 */
 	public String replaceVal(String sql,CbsBkCertificatesEntity t){
 		sql  = sql.replace("#{id}",String.valueOf(t.getId()));
 		sql  = sql.replace("#{name}",String.valueOf(t.getName()));
 		sql  = sql.replace("#{certificate_num}",String.valueOf(t.getCertificateNum()));
 		sql  = sql.replace("#{over_date}",String.valueOf(t.getOverDate()));
 		sql  = sql.replace("#{course_num}",String.valueOf(t.getCourseNum()));
 		sql  = sql.replace("#{id_num}",String.valueOf(t.getIdNum()));
 		sql  = sql.replace("#{create_name}",String.valueOf(t.getCreateName()));
 		sql  = sql.replace("#{create_by}",String.valueOf(t.getCreateBy()));
 		sql  = sql.replace("#{create_date}",String.valueOf(t.getCreateDate()));
 		sql  = sql.replace("#{update_name}",String.valueOf(t.getUpdateName()));
 		sql  = sql.replace("#{update_by}",String.valueOf(t.getUpdateBy()));
 		sql  = sql.replace("#{update_date}",String.valueOf(t.getUpdateDate()));
 		sql  = sql.replace("#{UUID}",UUID.randomUUID().toString());
 		return sql;
 	}
 	
	public void parseExcel(HttpServletRequest req) {
		FileBean fileBean = UploadFileUtil.UploadFile(req);
		try {
			ZyFileLogEntity fileLog  = new ZyFileLogEntity();
			fileLog.setBusinessType(StaticValue.IMPORT_FILE_BK_CERTIFICATE);			
			fileLog.setOldFileName(fileBean.getFileRealName());
			fileLog.setNewFileName(fileBean.getFileName());
			fileLog.setFilePath(fileBean.getSavePath());
			fileLog.setFileType("xlsx");
			fileLog.setStatus("0");
			super.save(fileLog);
			
			List<Map<String, Object>> mapList = ExcelUtil.analyzeExcel(fileLog.getFilePath(), "1");
			List<CbsBkCertificatesEntity> cbsbkEntutys = new ArrayList<CbsBkCertificatesEntity>();
			/*//查询巴可大学证书表里边的身份证号
			StringBuffer sb= new StringBuffer("select id_num from cbs_bk_certificates;");
			List<Map<String, Object>>  idnums=this.findForJdbc(sb.toString());
			for (int i = 0; i <idnums.size() ; i++) {
				Map<String, Object> idnum=idnums.get(i);
				System.out.println(idnum.get("id_num"));
			}*/
			for (int i = 1; i < mapList.size(); i++) {
				Map<String, Object> map = mapList.get(i);
	            String name = StringUtil.getString(map.get("0"));
                String idNum = StringUtil.getString(map.get("4"));
                if(StringUtil.isEmpty(name) || StringUtil.isEmpty(idNum)){
                    continue;
                }
                if(!StringUtil.isEmpty(idNum) && findHql("from CbsBkCertificatesEntity where idNum=? and isDelete=0",idNum).size()>1){
					continue;
                }
				CbsBkCertificatesEntity cbsbkEntity = new CbsBkCertificatesEntity();
				cbsbkEntity.setName(name);
				cbsbkEntity.setCertificateNum(StringUtil.getString(map.get("1")));
				cbsbkEntity.setOverDate(DateUtils.str2Date(StringUtil.getString(map.get("2")),DataUtils.sdf_date_1));
				cbsbkEntity.setCourseNum(StringUtil.getString(map.get("3")));
				cbsbkEntity.setIdNum(idNum);
				cbsbkEntutys.add(cbsbkEntity);
                cbsbkEntity.setIsDelete("0");
			}
			super.batchSave(cbsbkEntutys);
		} catch (Exception e) {
			e.printStackTrace();
			LogUtil.error("parseExcel",e);
		}
	}
 
}